﻿using MxWeiXinPF.Common;
using MxWeiXinPF.DBUtility;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MxWeiXinPF.DAL
{
    public partial class sendmessage
    {
        private string databaseprefix;

        public sendmessage(string _databaseprefix)
        {
            this.databaseprefix = _databaseprefix;
        }

        public int Add(MxWeiXinPF.Model.sendmessage model)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("insert into " + this.databaseprefix + "SendMessage(");
            builder.Append("[truename],[mobile],[productname],[PurchaseQty],[UsedQty],[PurchaseDate],[SendDate],[SendToDate],[MessageContent],[isSend],[tqts],[wid],[openid],[shopid],[orderid],[userid],[extInt],[extInt1],[extStr],[extStr1],[extStr2])");
            builder.Append(" values (");
            builder.Append("@truename,@mobile,@productname,@PurchaseQty,@UsedQty,@PurchaseDate,@SendDate,DateAdd(Day,@PurchaseQty/@UsedQty -@tqts,@PurchaseDate),@MessageContent,@tqts,@wid,@openid,@shopid,@orderid,@userid,@extInt,@extInt1,@extStr,@extStr1,@extStr2)");
            builder.Append(";select @@IDENTITY");
            SqlParameter[] cmdParms = new SqlParameter[] { 
                new SqlParameter("@truename", SqlDbType.VarChar, 50), new SqlParameter("@mobile", SqlDbType.VarChar, 50), new SqlParameter("@productname", SqlDbType.VarChar, 200), new SqlParameter("@PurchaseQty", SqlDbType.Int, 4), new SqlParameter("@UsedQty", SqlDbType.Int, 4), new SqlParameter("@PurchaseDate", SqlDbType.DateTime), new SqlParameter("@SendDate", SqlDbType.DateTime), new SqlParameter("@SendToDate", SqlDbType.DateTime), new SqlParameter("@MessageContent", SqlDbType.VarChar, 500), new SqlParameter("@tqts", SqlDbType.Int, 4), new SqlParameter("@wid", SqlDbType.Int, 4), new SqlParameter("@openid", SqlDbType.VarChar, 100), new SqlParameter("@shopid", SqlDbType.Int, 4), new SqlParameter("@orderid", SqlDbType.Int, 4), new SqlParameter("@userid", SqlDbType.Int, 4), new SqlParameter("@extInt", SqlDbType.Int, 4), 
                new SqlParameter("@extInt1", SqlDbType.Int, 4), new SqlParameter("@extStr", SqlDbType.VarChar, 50), new SqlParameter("@extStr1", SqlDbType.VarChar, 100), new SqlParameter("@extStr2", SqlDbType.VarChar, 200)
             };
            cmdParms[0].Value = model.truename;
            cmdParms[1].Value = model.mobile;
            cmdParms[2].Value = model.productname;
            cmdParms[3].Value = model.PurchaseQty;
            cmdParms[4].Value = model.UsedQty;
            cmdParms[5].Value = model.PurchaseDate;
            cmdParms[6].Value = model.SendDate;
            cmdParms[7].Value = model.SendToDate;
            cmdParms[8].Value = model.MessageContent;
            cmdParms[9].Value = model.tqts;
            cmdParms[10].Value = model.wid;
            cmdParms[11].Value = model.openid;
            cmdParms[12].Value = model.shopid;
            cmdParms[13].Value = model.orderid;
            cmdParms[14].Value = model.userid;
            cmdParms[15].Value = model.extInt;
            cmdParms[0x10].Value = model.extInt1;
            cmdParms[0x11].Value = model.extStr;
            cmdParms[0x12].Value = model.extStr1;
            cmdParms[0x13].Value = model.extStr2;
            object single = DbHelperSQL.GetSingle(builder.ToString(), cmdParms);
            if (single == null)
            {
                return 0;
            }
            return Convert.ToInt32(single);
        }

        public int AddInfo(MxWeiXinPF.Model.sendmessage model)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("insert into " + this.databaseprefix + "SendMessage(");
            builder.Append("[truename],[mobile],[productname],[PurchaseQty],[UsedQty],[PurchaseDate],[SendDate],[MessageContent],[tqts],[wid],[shopid])");
            builder.Append(" values (");
            builder.Append("@truename,@mobile,@productname,@PurchaseQty,@UsedQty,@PurchaseDate,DateAdd(Day,@PurchaseQty/@UsedQty -@tqts,@PurchaseDate),@MessageContent,@tqts,@wid,@shopid)");
            builder.Append(";select @@IDENTITY");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@truename", SqlDbType.VarChar, 50), new SqlParameter("@mobile", SqlDbType.VarChar, 50), new SqlParameter("@productname", SqlDbType.VarChar, 200), new SqlParameter("@PurchaseQty", SqlDbType.Int, 4), new SqlParameter("@UsedQty", SqlDbType.Int, 4), new SqlParameter("@PurchaseDate", SqlDbType.DateTime), new SqlParameter("@MessageContent", SqlDbType.VarChar, 500), new SqlParameter("@tqts", SqlDbType.Int, 4), new SqlParameter("@wid", SqlDbType.Int, 4), new SqlParameter("@shopid", SqlDbType.Int, 4) };
            cmdParms[0].Value = model.truename;
            cmdParms[1].Value = model.mobile;
            cmdParms[2].Value = model.productname;
            cmdParms[3].Value = model.PurchaseQty;
            cmdParms[4].Value = model.UsedQty;
            cmdParms[5].Value = model.PurchaseDate;
            cmdParms[6].Value = model.MessageContent;
            cmdParms[7].Value = model.tqts;
            cmdParms[8].Value = model.wid;
            cmdParms[9].Value = model.shopid;
            object single = DbHelperSQL.GetSingle(builder.ToString(), cmdParms);
            if (single == null)
            {
                return 0;
            }
            return Convert.ToInt32(single);
        }

        public bool Delete(int id)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("delete from " + this.databaseprefix + "SendMessage ");
            builder.Append(" where id=@id");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int, 4) };
            cmdParms[0].Value = id;
            return (DbHelperSQL.ExecuteSql(builder.ToString(), cmdParms) > 0);
        }

        public bool Exists(int id)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select count(1) from " + this.databaseprefix + "SendMessage");
            builder.Append(" where id=@id ");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int, 4) };
            cmdParms[0].Value = id;
            return DbHelperSQL.Exists(builder.ToString(), cmdParms);
        }

        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select ");
            if (Top > 0)
            {
                builder.Append(" top " + Top.ToString());
            }
            builder.Append(" * ");
            builder.Append(" FROM " + this.databaseprefix + "SendMessage ");
            if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
            }
            builder.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(builder.ToString());
        }

        public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select * FROM " + this.databaseprefix + "SendMessage");
            if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
            }
            recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(builder.ToString())));
            return DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, builder.ToString(), filedOrder));
        }

        public MxWeiXinPF.Model.sendmessage GetModel(int id)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select  top 1 * from " + this.databaseprefix + "SendMessage ");
            builder.Append(" where id=@id");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int, 4) };
            cmdParms[0].Value = id;
            MxWeiXinPF.Model.sendmessage sendmessage = new MxWeiXinPF.Model.sendmessage();
            DataSet set = DbHelperSQL.Query(builder.ToString(), cmdParms);
            if (set.Tables[0].Rows.Count > 0)
            {
                if (set.Tables[0].Rows[0]["id"].ToString() != "")
                {
                    sendmessage.id = int.Parse(set.Tables[0].Rows[0]["id"].ToString());
                }
                sendmessage.truename = set.Tables[0].Rows[0]["truename"].ToString();
                sendmessage.mobile = set.Tables[0].Rows[0]["mobile"].ToString();
                sendmessage.productname = set.Tables[0].Rows[0]["productname"].ToString();
                if (set.Tables[0].Rows[0]["PurchaseQty"].ToString() != "")
                {
                    sendmessage.PurchaseQty = int.Parse(set.Tables[0].Rows[0]["PurchaseQty"].ToString());
                }
                if (set.Tables[0].Rows[0]["UsedQty"].ToString() != "")
                {
                    sendmessage.UsedQty = int.Parse(set.Tables[0].Rows[0]["UsedQty"].ToString());
                }
                if (set.Tables[0].Rows[0]["PurchaseDate"].ToString() != "")
                {
                    sendmessage.PurchaseDate = DateTime.Parse(set.Tables[0].Rows[0]["PurchaseDate"].ToString());
                }
                if (set.Tables[0].Rows[0]["CreateTime"].ToString() != "")
                {
                    sendmessage.CreateTime = DateTime.Parse(set.Tables[0].Rows[0]["CreateTime"].ToString());
                }
                if (set.Tables[0].Rows[0]["SendDate"].ToString() != "")
                {
                    sendmessage.SendDate = DateTime.Parse(set.Tables[0].Rows[0]["SendDate"].ToString());
                }
                if (set.Tables[0].Rows[0]["SendToDate"].ToString() != "")
                {
                    sendmessage.SendToDate = DateTime.Parse(set.Tables[0].Rows[0]["SendToDate"].ToString());
                }
                sendmessage.MessageContent = set.Tables[0].Rows[0]["MessageContent"].ToString();
                sendmessage.isSend = set.Tables[0].Rows[0]["isSend"].ToString();
                if (set.Tables[0].Rows[0]["tqts"].ToString() != "")
                {
                    sendmessage.tqts = int.Parse(set.Tables[0].Rows[0]["tqts"].ToString());
                }
                if (set.Tables[0].Rows[0]["wid"].ToString() != "")
                {
                    sendmessage.wid = int.Parse(set.Tables[0].Rows[0]["wid"].ToString());
                }
                sendmessage.openid = set.Tables[0].Rows[0]["openid"].ToString();
                if (set.Tables[0].Rows[0]["shopid"].ToString() != "")
                {
                    sendmessage.shopid = int.Parse(set.Tables[0].Rows[0]["shopid"].ToString());
                }
                if (set.Tables[0].Rows[0]["orderid"].ToString() != "")
                {
                    sendmessage.orderid = int.Parse(set.Tables[0].Rows[0]["orderid"].ToString());
                }
                if (set.Tables[0].Rows[0]["userid"].ToString() != "")
                {
                    sendmessage.userid = int.Parse(set.Tables[0].Rows[0]["userid"].ToString());
                }
                if (set.Tables[0].Rows[0]["extInt"].ToString() != "")
                {
                    sendmessage.extInt = int.Parse(set.Tables[0].Rows[0]["extInt"].ToString());
                }
                if (set.Tables[0].Rows[0]["extInt1"].ToString() != "")
                {
                    sendmessage.extInt1 = int.Parse(set.Tables[0].Rows[0]["extInt1"].ToString());
                }
                sendmessage.extStr = set.Tables[0].Rows[0]["extStr"].ToString();
                sendmessage.extStr = set.Tables[0].Rows[0]["extStr1"].ToString();
                sendmessage.extStr2 = set.Tables[0].Rows[0]["extStr2"].ToString();
                return sendmessage;
            }
            return null;
        }

        public DataSet GetSmsMessage()
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select ");
            builder.Append(" id,user_id,express_time,telphone,mobile,add_time,confirm_time,complete_time,wid,openid,shopid,parentid ");
            builder.Append(" FROM " + this.databaseprefix + "orders ");
            builder.Append(" where  isSend='0' and isnull(openid,'')='' and mobile<>'' and isnull(SendDate,'')<=CONVERT(varchar(10) , getdate(), 120) ");
            return DbHelperSQL.Query(builder.ToString());
        }

        public DataSet GetTemplateMessage()
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select ");
            builder.Append(" a.id,a.truename,a.mobile,a.productname,isnull(a.MessageContent,'') MessageContent,a.openid,a.shopid,a.SendDate,isnull(b.nick_name,a.truename) nick_name ");
            builder.Append(" FROM " + this.databaseprefix + "SendMessage a");
            builder.Append(" left join " + this.databaseprefix + "users b on a.openid =b.wxopenid ");
            builder.Append(" where  a.isSend='0'  and  isnull(a.SendDate,'')<=CONVERT(varchar(10) , getdate(), 120) ");
            return DbHelperSQL.Query(builder.ToString());
        }

        public int InsertSendSMSmore(string Phone, string MessageContent, int numcount, int wid, int shopid)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("insert into " + this.databaseprefix + "SendMessage(");
            builder.Append("[mobile],[SendToDate],[MessageContent],[qty],[wid],[shopid])");
            builder.Append(" values (");
            builder.Append("@mobile,getdate(),@MessageContent,@qty,@wid,@shopid)");
            builder.Append(";select @@IDENTITY");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@mobile", SqlDbType.Text), new SqlParameter("@MessageContent", SqlDbType.VarChar, 500), new SqlParameter("@qty", SqlDbType.Int, 4), new SqlParameter("@wid", SqlDbType.Int, 4), new SqlParameter("@shopid", SqlDbType.Int, 4) };
            cmdParms[0].Value = Phone;
            cmdParms[1].Value = MessageContent;
            cmdParms[2].Value = numcount;
            cmdParms[3].Value = wid;
            cmdParms[4].Value = shopid;
            object single = DbHelperSQL.GetSingle(builder.ToString(), cmdParms);
            if (single == null)
            {
                return 0;
            }
            return Convert.ToInt32(single);
        }

        public bool isAllowSend(int shopid)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select count(1) from " + this.databaseprefix + "manager");
            builder.Append(" where id=@id and isSendMessage='1'");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int, 4) };
            cmdParms[0].Value = shopid;
            return DbHelperSQL.Exists(builder.ToString(), cmdParms);
        }

        public bool Update(MxWeiXinPF.Model.sendmessage model)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("update " + this.databaseprefix + "SendMessage set ");
            builder.Append("truename=@truename,");
            builder.Append("mobile=@mobile,");
            builder.Append("productname=@productname,");
            builder.Append("PurchaseQty=@PurchaseQty,");
            builder.Append("UsedQty=@UsedQty,");
            builder.Append("PurchaseDate=@PurchaseDate,");
            builder.Append("SendDate=DateAdd(Day,@PurchaseQty/@UsedQty -@tqts,@PurchaseDate),");
            builder.Append("SendToDate=@SendToDate,");
            builder.Append("MessageContent=@MessageContent,");
            builder.Append("tqts=@tqts,");
            builder.Append("wid=@wid,");
            builder.Append("openid=@openid,");
            builder.Append("shopid=@shopid,");
            builder.Append("orderid=@orderid,");
            builder.Append("userid=@userid,");
            builder.Append("extInt=@extInt,");
            builder.Append("extInt1=@extInt1,");
            builder.Append("extStr=@extStr,");
            builder.Append("extStr1=@extStr1,");
            builder.Append("extStr2=@extStr2");
            builder.Append(" where id=@id");
            SqlParameter[] cmdParms = new SqlParameter[] { 
                new SqlParameter("@truename", SqlDbType.VarChar, 50), new SqlParameter("@mobile", SqlDbType.VarChar, 50), new SqlParameter("@productname", SqlDbType.VarChar, 200), new SqlParameter("@PurchaseQty", SqlDbType.Int, 4), new SqlParameter("@UsedQty", SqlDbType.Int, 4), new SqlParameter("@PurchaseDate", SqlDbType.DateTime), new SqlParameter("@SendDate", SqlDbType.DateTime), new SqlParameter("@SendToDate", SqlDbType.DateTime), new SqlParameter("@MessageContent", SqlDbType.VarChar, 500), new SqlParameter("@tqts", SqlDbType.Int, 4), new SqlParameter("@wid", SqlDbType.Int, 4), new SqlParameter("@openid", SqlDbType.VarChar, 100), new SqlParameter("@shopid", SqlDbType.Int, 4), new SqlParameter("@orderid", SqlDbType.Int, 4), new SqlParameter("@userid", SqlDbType.Int, 4), new SqlParameter("@extInt", SqlDbType.Int, 4), 
                new SqlParameter("@extInt1", SqlDbType.Int, 4), new SqlParameter("@extStr", SqlDbType.VarChar, 50), new SqlParameter("@extStr1", SqlDbType.VarChar, 100), new SqlParameter("@extStr2", SqlDbType.VarChar, 200), new SqlParameter("@id", SqlDbType.Int, 4)
             };
            cmdParms[0].Value = model.truename;
            cmdParms[1].Value = model.mobile;
            cmdParms[2].Value = model.productname;
            cmdParms[3].Value = model.PurchaseQty;
            cmdParms[4].Value = model.UsedQty;
            cmdParms[5].Value = model.PurchaseDate;
            cmdParms[6].Value = model.SendDate;
            if (model.SendToDate < new DateTime(1970, 1, 1))
            {
                cmdParms[7].Value = new DateTime(1970, 1, 1);
            }
            else {
                cmdParms[7].Value = model.SendToDate;
            }
            
            cmdParms[8].Value = model.MessageContent;
            cmdParms[9].Value = model.tqts;
            cmdParms[10].Value = model.wid;
            cmdParms[11].Value = model.openid;
            cmdParms[12].Value = model.shopid;
            cmdParms[13].Value = model.orderid;
            cmdParms[14].Value = model.userid;
            cmdParms[15].Value = model.extInt;
            cmdParms[0x10].Value = model.extInt1;
            cmdParms[0x11].Value = model.extStr;
            cmdParms[0x12].Value = model.extStr1;
            cmdParms[0x13].Value = model.extStr2;
            cmdParms[20].Value = model.id;
            return (DbHelperSQL.ExecuteSql(builder.ToString(), cmdParms) > 0);
        }

        public int UpdateField(int id, string strValue)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("update " + this.databaseprefix + "SendMessage set " + strValue);
            builder.Append(" where id=" + id);
            return DbHelperSQL.ExecuteSql(builder.ToString());
        }

        public bool UpdateInfo(MxWeiXinPF.Model.sendmessage model)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("update " + this.databaseprefix + "SendMessage set ");
            builder.Append("truename=@truename,");
            builder.Append("mobile=@mobile,");
            builder.Append("productname=@productname,");
            builder.Append("PurchaseQty=@PurchaseQty,");
            builder.Append("UsedQty=@UsedQty,");
            builder.Append("PurchaseDate=@PurchaseDate,");
            builder.Append("SendDate=DateAdd(Day,@PurchaseQty/@UsedQty -@tqts,@PurchaseDate),");
            builder.Append("MessageContent=@MessageContent,");
            builder.Append("tqts=@tqts,");
            builder.Append("wid=@wid,");
            builder.Append("shopid=@shopid");
            builder.Append(" where id=@id");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@truename", SqlDbType.VarChar, 50), new SqlParameter("@mobile", SqlDbType.VarChar, 50), new SqlParameter("@productname", SqlDbType.VarChar, 200), new SqlParameter("@PurchaseQty", SqlDbType.Int, 4), new SqlParameter("@UsedQty", SqlDbType.Int, 4), new SqlParameter("@PurchaseDate", SqlDbType.DateTime), new SqlParameter("@MessageContent", SqlDbType.VarChar, 500), new SqlParameter("@tqts", SqlDbType.Int, 4), new SqlParameter("@wid", SqlDbType.Int, 4), new SqlParameter("@shopid", SqlDbType.Int, 4), new SqlParameter("@id", SqlDbType.Int, 4) };
            cmdParms[0].Value = model.truename;
            cmdParms[1].Value = model.mobile;
            cmdParms[2].Value = model.productname;
            cmdParms[3].Value = model.PurchaseQty;
            cmdParms[4].Value = model.UsedQty;
            cmdParms[5].Value = model.PurchaseDate;
            cmdParms[6].Value = model.MessageContent;
            cmdParms[7].Value = model.tqts;
            cmdParms[8].Value = model.wid;
            cmdParms[9].Value = model.shopid;
            cmdParms[10].Value = model.id;
            return (DbHelperSQL.ExecuteSql(builder.ToString(), cmdParms) > 0);
        }

        public bool UpdateIsSend(string issend, DateTime dt, int id)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("update " + this.databaseprefix + "SendMessage set ");
            builder.Append("issend=@issend,");
            builder.Append("SendToDate=@SendToDate");
            builder.Append(" where id=@id");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@issend", SqlDbType.VarChar, 50), new SqlParameter("@SendToDate", SqlDbType.DateTime), new SqlParameter("@id", SqlDbType.Int, 4) };
            cmdParms[0].Value = issend;
            cmdParms[1].Value = dt;
            cmdParms[2].Value = id;
            return (DbHelperSQL.ExecuteSql(builder.ToString(), cmdParms) > 0);
        }
    }
}
